package com.dayu.helper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SQLServerHelper {

    /***
     * 数据库链接串（动态）
     */
    public static String DataBaseContentUrl = "jdbc:sqlserver://%s";
    /***
     * 驱动类
     */
    public static final String DriverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    /***
     * 获取数据库库列表
     * @return
     * @throws SQLException
     */
    public static List<String> QueryDataBaseList() throws SQLException {
        DataBaseContentUrl = String.format("jdbc:sqlserver://%s;databaseName=%s", EntryHelper.DataBaseUrl,EntryHelper.DataBaseInit);

        List<String> listDatabase = new ArrayList<>();
        Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')");
        while (resultSet.next()) {
            String dbName = resultSet.getString("name");
            listDatabase.add(dbName);
        }
        connection.close();
        return listDatabase;
    }

    /***
     * 获取库下所有表
     * 格式：表名 - 表描述
     * @return
     * @throws SQLException
     */
    public static List<String> QueryAllTable() throws SQLException {
        DataBaseContentUrl = String.format("jdbc:sqlserver://%s;databaseName=%s", EntryHelper.DataBaseUrl, EntryHelper.DataBaseSele);

        List<String> listTable = new ArrayList<>();
        Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
                "SELECT TABLE_NAME, TABLE_COMMENT " +
                        "FROM INFORMATION_SCHEMA.TABLES " +
                        "LEFT JOIN INFORMATION_SCHEMA.TABLE_COMMENTS " +
                        "ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.TABLE_COMMENTS.TABLE_NAME " +
                        "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '" + EntryHelper.DataBaseSele + "'");
        while (resultSet.next()) {
            String tableName = resultSet.getString("TABLE_NAME");
            String tableComment = resultSet.getString("TABLE_COMMENT");
            listTable.add(tableName + " - " + (tableComment != null ? tableComment : ""));
        }
        connection.close();
        return listTable;
    }

    /**
     * 测试数据库连接
     * @return
     * @throws SQLException
     */
    public static boolean verifyConnection() throws SQLException {
        try {
            DataBaseContentUrl = String.format("jdbc:sqlserver://%s;databaseName=%s", EntryHelper.DataBaseUrl,EntryHelper.DataBaseInit);
            Connection connection = DriverManager.getConnection(DataBaseContentUrl, EntryHelper.DataBaseUser, EntryHelper.DataBasePwd);
            if (connection != null) {
                connection.close();
                return true;
            }
        } catch (SQLException e) {
            throw e;
        }
        return false;
    }
}
